package com.weatherquery.dao;

import com.weatherquery.informbean.WeatherData;
import com.weatherquery.utils.DruidUtils;
import com.weatherquery.utils.dbHelper;
import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.handlers.ScalarHandler;

import java.sql.*;
import java.util.ArrayList;
import java.util.List;

import static com.weatherquery.utils.dbHelper.getConnection;

/**
 * @Author Natural-Pride
 * @Date 2023/12/17 10:12
 * @Description 实现用于操作数据库中的天气信息的方法。主要功能包括：
 * 1. 向数据库中插入实时天气信息及未来五天天气预报信息。(insertWeatherData)。
 *    手动向数据库中插入天气信息(insertWeatehr)
 * 2. 查询数据库中所有天气信息(selectAllWeather)。
 * 3. 根据城市ID查询数据库中指定城市的天气信息(selectWeatherById)。
 * 4. 根据城市名称查询数据库中指定城市的天气信息(searchWeatherByName)。
 * 5. 根据城市ID删除数据库中指定城市的天气信息(deleteWeatherById)。
 * 6. 根据城市ID修改数据库中指定城市的天气信息(updateWeather)。
 * 7. 根据城市名称分页查询数据库中指定城市的天气信息(displayWeather)。
 * 8. 查询数据库中所有城市的天气信息的总数(selectWeatherCount)。
 * @Param pageNum:页码
 * @Param pageSize:每页显示的条数
 */
public class WeatherDAO {
    public static Connection connection;
    private static Statement statement;
    private static ResultSet resultSet;
    private WeatherData weatherData;

    /**
     * 调用百度天气api向数据库中添加全国省会城市实时天气信息及未来五天天气预报信息
     */
    public void insertWeatherData(WeatherData weatherData) {
        //尝试建立数据库连接
        try {
            connection = getConnection();
            //定义插入的 SQL语句
            String sql = "insert into weather_data(cityId,cityName,nowWeather,nowTemp,nowWindClass,nowWindDir,nowUptime,forecastDate,forecastWeek," +
                    "forecastWeather,forecastHigh,forecastLow) values(?,?,?,?,?,?,?,?,?,?,?,?) ";
            //通过数据库连接创建预编译的语句(PreparedStatement)
            PreparedStatement preparedStatement = connection.prepareStatement(sql);
            //通过预编译语句的 set方法设置参数值
            preparedStatement.setString(1, weatherData.getCityId());
            preparedStatement.setString(2, weatherData.getCity());
            preparedStatement.setString(3, weatherData.getNowWeather());
            preparedStatement.setString(4, weatherData.getNowTemp());
            preparedStatement.setString(5, weatherData.getNowWindClass());
            preparedStatement.setString(6, weatherData.getNowWindDir());
            preparedStatement.setString(7, weatherData.getNowUptime());
            preparedStatement.setString(8, weatherData.getForecastDate());
            preparedStatement.setString(9, weatherData.getForecastWeek());
            preparedStatement.setString(10, weatherData.getForecastWeather());
            preparedStatement.setString(11, weatherData.getForecastHigh());
            preparedStatement.setString(12, weatherData.getForecastLow());

            //执行更新操作
            int i = preparedStatement.executeUpdate();
            //根据返回的影响行数判断插入是否成功
            if (i > 0) {
                System.out.println("插入成功!");
            } else {
                System.out.println("插入失败!");
            }
        } catch (SQLException e) {
            //异常处理,抛出运行时异常
            throw new RuntimeException(e);
        } finally {
            //关闭数据库连接
            dbHelper.close(resultSet, statement, connection);
        }

    }

    /**
     * 手动添加天气数据至数据库
     */
    public int insertWeather(WeatherData weatherData) {
        int i = 0;
        try {
            String sql = "insert into weather_data(cityId,cityName,nowWeather,nowTemp,nowWindClass,nowWindDir,nowUptime,forecastDate,forecastWeek," +
                    "forecastWeather,forecastHigh,forecastLow) values(?,?,?,?,?,?,?,?,?,?,?,?) ";
            QueryRunner queryRunner = new QueryRunner(DruidUtils.getDataSource());
            Object[] params = {
                    weatherData.getCityId(), weatherData.getCity(),
                    weatherData.getNowWeather(), weatherData.getNowTemp(),
                    weatherData.getNowWindClass(), weatherData.getNowWindDir(),
                    weatherData.getNowUptime(), weatherData.getForecastDate(),
                    weatherData.getForecastWeek(), weatherData.getNowWeather(),
                    weatherData.getForecastHigh(), weatherData.getForecastLow()};
            i = queryRunner.update(sql, params);
        } catch (Exception e) {
            throw new RuntimeException(e);
        } finally {
            dbHelper.close(resultSet, statement, connection);
        }
        return i;
    }

    /**
     * 查询所有城市天气
     * 从数据库中选择出所有的天气信息,并以 List的形式返回
     *
     * @return weatherList
     */
    public List<WeatherData> selectAllWeather() {
        //创建一个空的 List用于存储天气信息
        List<WeatherData> weatherList = new ArrayList<>();
        try {
            //获取数据库的连接
            connection = getConnection();
            //创建一个 Statement对象用于执行 SQL语句
            statement = connection.createStatement();
            //构建 SQL查询语句,选择出所有的天气信息,并按城市编号排序
            String sql = "select* from weather_data group by cityId order by cityId";
            //执行 SQL语句,获取查询结果
            resultSet = statement.executeQuery(sql);
            //遍历查询结果,将每一条天气信息添加到 List中
            while (resultSet.next()) {
                weatherList.add(new WeatherData(
                        resultSet.getString("cityId"),
                        resultSet.getString("cityName"),
                        resultSet.getString("nowWeather"),
                        resultSet.getString("nowTemp"),
                        resultSet.getString("nowWindClass"),
                        resultSet.getString("nowWindDir"),
                        resultSet.getString("nowUpTime"),
                        resultSet.getString("forecastDate"),
                        resultSet.getString("forecastWeek"),
                        resultSet.getString("forecastWeather"),
                        resultSet.getString("forecastHigh"),
                        resultSet.getString("forecastLow")
                ));
            }
        } catch (SQLException e) {
            //如果发生了 SQL异常,抛出运行时异常
            throw new RuntimeException(e);
        } finally {
            dbHelper.close(resultSet, statement, connection);
        }
        //返回存储了天气信息的 List
        return weatherList;
    }

    /**
     * 根据城市ID查询城市天气信息
     */
    public WeatherData selectWeatherById(String cityId) {
        WeatherData weather = null;
        try {
            connection = dbHelper.getConnection();
            String sql = "select cityId ,cityName,nowWeather,nowTemp,nowWindClass,nowWindDir,nowUpTime ," +
                    "forecastDate ,forecastWeek,forecastWeather,forecastHigh,forecastLow from weather_data where cityId=?";
/*          QueryRunner = new QueryRunner(DruidUtils.getDataSource());
            weather = queryRunner.query(sql, new BeanHandler<InformBean>(InformBean.class), cityId);*/
            PreparedStatement statement = connection.prepareStatement(sql);
            statement.setString(1, cityId);
            ResultSet resultSet = statement.executeQuery();
            while (resultSet.next()) {
                weather = new WeatherData();
                weather.setCityId(resultSet.getString("cityId"));
                weather.setCity(resultSet.getString("cityName"));
                weather.setNowWeather(resultSet.getString("nowWeather"));
                weather.setNowTemp(resultSet.getString("nowTemp"));
                weather.setNowWindClass(resultSet.getString("nowWindClass"));
                weather.setNowWindDir(resultSet.getString("nowWindDir"));
                weather.setNowUptime(resultSet.getString("nowUpTime"));
                weather.setForecastDate(resultSet.getString("forecastDate"));
                weather.setForecastWeek(resultSet.getString("forecastWeek"));
                weather.setForecastWeather(resultSet.getString("forecastWeather"));
                weather.setForecastHigh(resultSet.getString("forecastHigh"));
                weather.setForecastLow(resultSet.getString("forecastLow"));
            }
        } catch (SQLException e) {
            throw new RuntimeException(e);
        } finally {
            dbHelper.close(resultSet, statement, connection);
        }
        return weather;
    }

    /**
     * 根据城市名称搜索城市天气信息
     */
    public List<WeatherData> searchWeatherByName(String cityName) {
        //创建一个空的 List用于存储天气信息
        List<WeatherData> weatherSearchList = new ArrayList<>();
        try {
            //获取数据库的连接
            connection = getConnection();
            //创建一个 Statement对象用于执行 SQL语句
            String sql = "select * from weather_data where cityName like ?";
            //执行 SQL语句,获取查询结果
            PreparedStatement ps = connection.prepareStatement(sql);
            ps.setString(1, "%" + cityName + "%");
            resultSet = ps.executeQuery();
            WeatherData weather = null;
            //遍历查询结果,将每一条天气信息添加到 List中
            while (resultSet.next()) {
                weather = new WeatherData();
                weather.setCityId(resultSet.getString("cityId"));
                weather.setCity(resultSet.getString("cityName"));
                weather.setNowWeather(resultSet.getString("nowWeather"));
                weather.setNowTemp(resultSet.getString("nowTemp"));
                weather.setNowWindClass(resultSet.getString("nowWindClass"));
                weather.setNowWindDir(resultSet.getString("nowWindDir"));
                weather.setNowUptime(resultSet.getString("nowUpTime"));
                weather.setForecastDate(resultSet.getString("forecastDate"));
                weather.setForecastWeek(resultSet.getString("forecastWeek"));
                weather.setForecastWeather(resultSet.getString("forecastWeather"));
                weather.setForecastHigh(resultSet.getString("forecastHigh"));
                weather.setForecastLow(resultSet.getString("forecastLow"));
                weatherSearchList.add(weather);
            }
        } catch (SQLException e) {
            //如果发生了 SQL异常,抛出运行时异常
            throw new RuntimeException(e);
        } finally {
            dbHelper.close(resultSet, statement, connection);
        }
        //返回存储了天气信息的 List
        return weatherSearchList;
    }

    /**
     * cityId 根据城市ID删除城市信息
     */
    public int deleteWeatherById(String cityId) {
        int i = 0;
        try {
            connection = getConnection();
            String sql = "delete from weather_data where cityId=?";
            QueryRunner queryRunner = new QueryRunner(DruidUtils.getDataSource());
            // 向sql语句中添加参数
            i = queryRunner.update(sql, cityId);
        } catch (SQLException e) {
            throw new RuntimeException(e);
        } finally {
            dbHelper.close(resultSet, statement, connection);
        }
        return i;
    }

    /**
     * weather 根据城市ID修改城市天气信息
     */
    public int updateWeather(WeatherData weather) {
        int i = 0;
        try {
            connection = getConnection();
            statement = connection.createStatement();
            //定义插入的 SQL语句
            String sql = "update weather_data set cityId=?,cityName=?,nowWeather=?," +
                    "nowTemp=?,nowWindClass=?,nowWindDir=?,nowUpTime=?, forecastDate=?,forecastWeek=?," +
                    "forecastWeather=?,forecastHigh=?,forecastLow=? where cityId=?";
            //通过数据库连接创建预编译的语句(PreparedStatement)
            QueryRunner queryRunner = new QueryRunner(DruidUtils.getDataSource());
//        PreparedStatement ps= connection.prepareStatement(sql);
//        ps.setString(1, weather.getCityId());
//        ps.setString(2, weather.getCity());
//        ps.setString(3, weather.getNowWeather());
//        ps.setString(4, weather.getNowTemp());
//        ps.setString(5, weather.getNowWindClass());
//        ps.setString(6, weather.getNowWindDir());
//        ps.setString(7, weather.getNowUptime());
//        ps.setString(8, weather.getForecastDate());
//        ps.setString(9, weather.getForecastWeek());
//        ps.setString(10, weather.getForecastWeather());
//        ps.setString(11, weather.getForecastHigh());
//        ps.setString(12, weather.getForecastLow());
//        ps.setString(13, weather.getCityId());
//        i = ps.executeUpdate();
            Object[] params = {
                    weather.getCityId(), weather.getCity(), weather.getNowWeather(), weather.getNowTemp(), weather.getNowWindClass(),
                    weather.getNowWindDir(), weather.getNowUptime(), weather.getForecastDate(), weather.getForecastWeek(),
                    weather.getForecastWeather(), weather.getForecastHigh(), weather.getForecastLow(), weather.getCityId()
            };
            i = queryRunner.update(sql, params);
        } catch (SQLException e) {
            throw new RuntimeException(e);
        }
        return i;
    }

    /**
     * 分页查询天气信息
     * start 查询数据的起始行索引
     * limit 最多返回的数据记录数
     */
    public List<WeatherData> displayWeather(int start, int limit) {
        //创建一个空的 List用于存储天气信息
        List<WeatherData> displayWeatherList = new ArrayList<>();
        try {//获取数据库的连接
            connection = getConnection();
            //创建一个 Statement对象用于执行 SQL语句
            statement = connection.createStatement();
            //构建 SQL查询语句,选择出所有的天气信息,并按城市编号排序
            String sql = "select cityId, cityName,nowWeather,nowTemp,nowWindClass,nowWindDir,nowUptime,forecastDate," +
                    "forecastWeek,forecastWeather,forecastHigh,forecastLow from weather_data  order by cityId limit ?,? ";
            PreparedStatement statement = connection.prepareStatement(sql);
            statement.setInt(1, start);
            statement.setInt(2, limit);
            //执行查询,获取查询结果
            resultSet = statement.executeQuery();
            while (resultSet.next()) {
                WeatherData displayWeather = new WeatherData();
                displayWeather.setCityId(resultSet.getString("cityId"));
                displayWeather.setCity(resultSet.getString("cityName"));
                displayWeather.setNowWeather(resultSet.getString("nowWeather"));
                displayWeather.setNowTemp(resultSet.getString("nowTemp"));
                displayWeather.setNowWindClass(resultSet.getString("nowWindClass"));
                displayWeather.setNowWindDir(resultSet.getString("nowWindDir"));
                displayWeather.setNowUptime(resultSet.getString("nowUptime"));
                displayWeather.setForecastDate(resultSet.getString("forecastDate"));
                displayWeather.setForecastWeek(resultSet.getString("forecastWeek"));
                displayWeather.setForecastWeather(resultSet.getString("forecastWeather"));
                displayWeather.setForecastHigh(resultSet.getString("forecastHigh"));
                displayWeather.setForecastLow(resultSet.getString("forecastLow"));
                displayWeatherList.add(displayWeather);
            }
            //执行 SQL语句，获取查询结果
        } catch (SQLException e) {
            //如果发生了 SQL异常,抛出运行时异常
            e.printStackTrace();
        } finally {
            dbHelper.close(resultSet, statement, connection);
        }
        //返回存储了天气信息的 List
        return displayWeatherList;
    }

    /**
     * 分页查询——查询总记录数
     *
     * @return 记录总数
     */
    public long selectWeatherCount() {
        long count = 0;
        try {
            String sql = "select count(1) from weather_data";
            QueryRunner queryRunner = new QueryRunner(DruidUtils.getDataSource());
            count = queryRunner.query(sql, new ScalarHandler<Long>());
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return count;
    }
}
